package com.blue.jdbc.sql;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.blue.core.util.NumberUtil;
import com.blue.jdbc.annotation.CacheColumn;
import com.blue.jdbc.annotation.CacheEntity;
import com.blue.jdbc.annotation.CacheId;
import com.blue.jdbc.annotation.Parser;
import com.blue.jdbc.dialect.IDialect;

/**
 * SQL扩展工具类
 * 
 * @author zhengj
 * @since 1.0 2016年8月16日
 */
public class SqlExtendUtil
{
	private static String existTpl = "select count(*) from %s where %s";
	private static String getTpl = "select * from %s where %s";
	private static String getFieldTpl = "select %s from %s where %s";
	private static String incTpl = "update %s set %s where %s";
	private static String updateTpl = "update %s set %s where %s";
	private static String deleteTpl = "delete from %s where %s";
	private static String insertTpl = "insert into %s (%s) values (%s)";
	
	private static SqlExtendUtil sqlExtendUtil = new SqlExtendUtil();
	private IDialect dialect;
	private Parser parser = Parser.getInstance();
	
	private SqlExtendUtil()
	{
	}
	
	public static SqlExtendUtil getInstance()
	{
		return sqlExtendUtil;
	}
	
	public String incSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");

		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		List<String> idList = new ArrayList<>();
		
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			if (!NumberUtil.isNumber(params[i+1].getClass()))
				throw new IllegalArgumentException("参数第 " + (i+1) + " 个不是数字");
			
			String name = (String)params[i];
			CacheColumn column = columnMap.get(name);
			if (column == null)
				throw new IllegalArgumentException(name + " 不存在列");
			
			if (!NumberUtil.isNumber(column.getField().getType()))
				throw new IllegalArgumentException(name + " 不是数字列");

			columnList.add(dialect.escape(column.getColumn()) + "=" + dialect.escape(column.getColumn()) + "+?");
		}
		
		cacheEntity.getIdMap().forEach((k, v) -> idList.add(dialect.escape(v.getColumn()) + "=?"));
		
		String sql = String.format(incTpl, dialect.escape(cacheEntity.getTable()), 
				StringUtils.join(columnList, ", "), StringUtils.join(idList, " and "));
		return sql;
	}
	
	public String updateSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");

		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		List<String> idList = new ArrayList<>();
		
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			CacheColumn column = columnMap.get(name);
			if (column == null)
				throw new IllegalArgumentException(name + " 不存在列");

			columnList.add(dialect.escape(column.getColumn()) + "=:" + name);
		}
		
		cacheEntity.getIdMap().forEach((k, v) -> idList.add(dialect.escape(v.getColumn()) + "=:" + v.getName()));
		
		String sql = String.format(updateTpl, dialect.escape(cacheEntity.getTable()), 
				StringUtils.join(columnList, ", "), StringUtils.join(idList, " and "));
		return sql;
	}
	
	public String insertSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");
		
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		List<String> nameList = new ArrayList<>();
		
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		Map<String, CacheId> idMap = cacheEntity.getIdMap();
		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			String column = null;
			if (columnMap.get(name) != null)
				column = columnMap.get(name).getColumn();
			if (idMap.get(name) != null)
				column = idMap.get(name).getColumn();
			if (column == null)
				throw new IllegalArgumentException(name + " 不存在列");

			columnList.add(dialect.escape(column));
			nameList.add(":" + name);
		}
		
		String sql = String.format(insertTpl, dialect.escape(cacheEntity.getTable()), 
				StringUtils.join(columnList, ", "), StringUtils.join(nameList, ", "));
		return sql;
	}
	
	public String deleteSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");
		
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		Map<String, CacheId> idMap = cacheEntity.getIdMap();
		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			CacheColumn column = columnMap.get(name);
			if (column != null)
			{
				columnList.add(dialect.escape(column.getColumn()) + "=?");
				continue;
			}
			CacheId id = idMap.get(name);
			if (id != null)
			{
				columnList.add(dialect.escape(id.getColumn()) + "=?");
				continue;
			}
			throw new IllegalArgumentException(name + " 不存在列");
		}
		
		String sql = String.format(deleteTpl, dialect.escape(cacheEntity.getTable()), 
				StringUtils.join(columnList, " and "));
		return sql;
	}
	
	public String existSQL(Map<String, Object> map, Class<?> clazz, String...names)
	{
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();

		if (names.length > 0)
		{
			Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
			for (String name : names)
			{
				CacheColumn cacheColumn = columnMap.get(name);
				if (cacheColumn == null)
					throw new IllegalArgumentException(name + " 不存在列");
				
				columnList.add(dialect.escape(cacheColumn.getColumn()) + "=:" + name);
			}
		}
		
		String op = names.length > 0 ? "!=:" : "=:";
		cacheEntity.getIdMap().forEach((k, v) ->
		{
			Object val = map.get(v.getName());
			if (val != null && !val.equals(""))
			{
				columnList.add(dialect.escape(v.getColumn()) + op + k);
			}
		});
		
		String sql = String.format(existTpl, dialect.escape(cacheEntity.getTable()), StringUtils.join(columnList, " and "));
		return sql;
	}
	
	public String getFieldSQL(Class<?> clazz, String field, Object...params)
	{
		if (field == null || field.isEmpty())
			throw new IllegalArgumentException("field 不能为空");
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");
		
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		Map<String, CacheId> idMap = cacheEntity.getIdMap();
		
		String fieldColumn = null;
		if (columnMap.get(field) != null)
			fieldColumn = columnMap.get(field).getColumn();
		if (idMap.get(field) != null)
			fieldColumn = idMap.get(field).getColumn();
		if (fieldColumn == null)
			throw new IllegalArgumentException(field + " 不存在列");
		
		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			String whereColumn = null;
			if (columnMap.get(name) != null)
				whereColumn = columnMap.get(name).getColumn();
			if (idMap.get(name) != null)
				whereColumn = idMap.get(name).getColumn();
			if (whereColumn == null)
				throw new IllegalArgumentException(name + " 不存在列");

			columnList.add(dialect.escape(whereColumn) + "=?");
		}
		
		String sql = String.format(getFieldTpl, dialect.escape(fieldColumn), dialect.escape(cacheEntity.getTable()), StringUtils.join(columnList, " and "));
		return sql;
	}
	
	public String getSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");
		
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		Map<String, CacheId> idMap = cacheEntity.getIdMap();

		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			String whereColumn = null;
			if (columnMap.get(name) != null)
				whereColumn = columnMap.get(name).getColumn();
			if (idMap.get(name) != null)
				whereColumn = idMap.get(name).getColumn();
			if (whereColumn == null)
				throw new IllegalArgumentException(name + " 不存在列");

			columnList.add(dialect.escape(whereColumn) + "=:" + name);
		}
		
		String sql = String.format(getTpl, dialect.escape(cacheEntity.getTable()), StringUtils.join(columnList, " and "));
		return sql;
	}
	
	public String countSQL(Class<?> clazz, Object...params)
	{
		if (params.length == 0 || params.length % 2 != 0)
			throw new IllegalArgumentException("参数个数错误");
		
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<>();
		Map<String, CacheColumn> columnMap = cacheEntity.getColumnMap();
		Map<String, CacheId> idMap = cacheEntity.getIdMap();

		for (int i = 0; i < params.length; i+=2)
		{
			if (!(params[i] instanceof String))
				throw new IllegalArgumentException("参数第 " + i + " 个不是字符串的字段名称");
			
			String name = (String)params[i];
			String whereColumn = null;
			if (columnMap.get(name) != null)
				whereColumn = columnMap.get(name).getColumn();
			if (idMap.get(name) != null)
				whereColumn = idMap.get(name).getColumn();
			if (whereColumn == null)
				throw new IllegalArgumentException(name + " 不存在列");

			columnList.add(dialect.escape(whereColumn) + "=?");
		}
		
		String sql = String.format(existTpl, dialect.escape(cacheEntity.getTable()), StringUtils.join(columnList, " and "));
		return sql;
	}

	public void setDialect(IDialect dialect)
	{
		this.dialect = dialect;
	}
	
}
